﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Settings;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data;
using Utils;

namespace PanasonicWarranty.Business
{
    public partial class pa_WarrantyWaiting : BaseClass
    {
        public static List<pa_WarrantyWaiting> Paging(int currentPage, int pageSize, ref int totalRecord, int agenctyID,
                                    string modelCode, string productCode, string phoneNumber, DateTime? fromTime, DateTime? toTime,
                                    string warrantyType, bool? isValid, bool? approved)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                var resutl = context.pa_WarrantyWaitings.Where(p => p.AgencyID == agenctyID);
                if (!string.IsNullOrWhiteSpace(modelCode))
                {
                    resutl = resutl.Where(p => p.ModelCode.Contains(modelCode));
                }
                if (!string.IsNullOrWhiteSpace(productCode))
                {
                    resutl = resutl.Where(p => p.ProductCode.Contains(productCode));
                }
                if (!string.IsNullOrWhiteSpace(phoneNumber))
                {
                    resutl = resutl.Where(p => p.CustomerPhone.Contains(phoneNumber)
                                            || p.CustomerPhone.Contains(phoneNumber.ToNormalPhoneNumber()));
                }
                if (fromTime != null)
                {
                    resutl = resutl.Where(p => p.RegisterDate >= fromTime);
                }
                if (toTime != null)
                {
                    resutl = resutl.Where(p => p.RegisterDate <= toTime.Value.AddDays(1));
                }
                if (!string.IsNullOrEmpty(warrantyType))
                {
                    resutl = resutl.Where(p => p.WarrantyType == warrantyType);
                }
                if (isValid != null)
                {
                    resutl = resutl.Where(p => p.IsValid == isValid);
                }
                if (approved != null)
                {
                    resutl = resutl.Where(p => p.Approved == approved);
                }
                totalRecord = resutl.Count();
                return resutl.Skip(((currentPage - 1) < 0 ? 0 : (currentPage - 1)) * pageSize)
                            .Take(pageSize)
                            .OrderBy(p => p.RegisterDate).ToList();
            }
        }
        public static dynamic Paging(int currentPage, int pageSize, ref int totalRecord, int? agencyID,
                                    string category, string modelCode, string productCode, string phoneNumber, DateTime? fromTime, DateTime? toTime,
                                    bool? requiredHappyCall)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                var resutl = context.pa_WarrantyWaitings.Where(p => p.Approved == true);
                if (agencyID != null)
                {
                    resutl = resutl.Where(p => context.fn_Agency_GetAllChild(agencyID).Select(fn => fn.AgencyID).Contains(p.AgencyID));
                }
                if (!string.IsNullOrWhiteSpace(category))
                {
                    resutl = resutl.Where(p => context.pa_Models.Any(md => md.CateCode == category && md.ModelCode == p.ModelCode));
                }
                if (!string.IsNullOrWhiteSpace(modelCode))
                {
                    resutl = resutl.Where(p => p.ModelCode.Contains(modelCode));
                }
                if (!string.IsNullOrWhiteSpace(productCode))
                {
                    resutl = resutl.Where(p => p.ProductCode.Contains(productCode));
                }
                if (!string.IsNullOrWhiteSpace(phoneNumber))
                {
                    resutl = resutl.Where(p => p.CustomerPhone.Contains(phoneNumber)
                                            || p.CustomerPhone.Contains(phoneNumber.ToNormalPhoneNumber()));
                }
                if (fromTime != null)
                {
                    resutl = resutl.Where(p => p.RegisterDate >= fromTime);
                }
                if (toTime != null)
                {
                    resutl = resutl.Where(p => p.RegisterDate <= toTime.Value.AddDays(1));
                }
                if (requiredHappyCall != null)
                {
                    resutl = resutl.Where(p => context.pa_Models.Any(md => md.ApprovedRequired == requiredHappyCall && md.ModelCode == p.ModelCode));
                }
                totalRecord = resutl.Count();
                var retresul = resutl.Skip(((currentPage - 1) < 0 ? 0 : (currentPage - 1)) * pageSize)
                            .Take(pageSize)
                            .OrderBy(p => p.CreatedDate)
                            .Select(p => new
                            {
                                p.WarrantyWattingID,
                                p.ModelCode,
                                p.ProductCode,
                                p.AgencyID,
                                p.CustomerPhone,
                                p.WarrantyPackageCode,
                                p.RegisterDate,
                                p.WarrantyType,
                                Detail = p.WarrantyType == eWarrantyType.DK ?
                                        (context.pa_Models.FirstOrDefault(md => md.ModelCode == p.ModelCode).Description) :
                                        (context.pa_WarrantyPackages.FirstOrDefault(wp => wp.WarrantyPackageCode == p.WarrantyPackageCode).Description),
                                p.CreatedDate,
                                p.ModifiedDate,
                                RegisteredFrom = (string.IsNullOrEmpty(p.RegisteredPhone) ? p.RegisteredFrom : p.RegisteredPhone),
                                p.Description
                            });
                return retresul.ToList();
            }
        }
        
        public static dynamic GetAll(int? agencyID,
                                    string modelCode, string productCode, string phoneNumber, DateTime? fromTime, DateTime? toTime,
                                    bool? requiredHappyCall)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                var resutl = context.pa_WarrantyWaitings.Where(p => p.Approved == true);
                if (agencyID != null)
                {
                    resutl = resutl.Where(p => context.fn_Agency_GetAllChild(agencyID).Select(fn => fn.AgencyID).Contains(p.AgencyID));
                }
                if (!string.IsNullOrWhiteSpace(modelCode))
                {
                    resutl = resutl.Where(p => p.ModelCode.Contains(modelCode));
                }
                if (!string.IsNullOrWhiteSpace(productCode))
                {
                    resutl = resutl.Where(p => p.ProductCode.Contains(productCode));
                }
                if (!string.IsNullOrWhiteSpace(phoneNumber))
                {
                    resutl = resutl.Where(p => p.CustomerPhone.Contains(phoneNumber)
                                            || p.CustomerPhone.Contains(phoneNumber.ToNormalPhoneNumber()));
                }
                if (fromTime != null)
                {
                    resutl = resutl.Where(p => p.RegisterDate >= fromTime);
                }
                if (toTime != null)
                {
                    resutl = resutl.Where(p => p.RegisterDate <= toTime.Value.AddDays(1));
                }
                if (requiredHappyCall != null)
                {
                    resutl = resutl.Where(p => context.pa_Models.Any(md => md.ApprovedRequired == requiredHappyCall && md.ModelCode == p.ModelCode));
                }
                var retresul = resutl
                            .OrderBy(p => p.CreatedDate)
                            .Select(p => new
                            {
                                p.WarrantyWattingID,
                                p.ModelCode,
                                p.ProductCode,
                                p.AgencyID,
                                p.CustomerPhone,
                                p.WarrantyPackageCode,
                                p.RegisterDate,
                                p.WarrantyType,
                                Detail = p.WarrantyType == eWarrantyType.DK ?
                                        (context.pa_Models.FirstOrDefault(md => md.ModelCode == p.ModelCode).Description) :
                                        (context.pa_WarrantyPackages.FirstOrDefault(wp => wp.WarrantyPackageCode == p.WarrantyPackageCode).Description),
                                p.CreatedDate,
                                p.ModifiedDate,
                                RegisteredFrom = (string.IsNullOrEmpty(p.RegisteredPhone) ? p.RegisteredFrom : p.RegisteredPhone),
                                p.Description
                            });
                return retresul.ToList();
            }
        }

        public static DataTable GetAll(int agencyID)
        {
            string query = @"
                    SELECT pw.ModelCode, pw.ProductCode, pw.CustomerPhone, pw.WarrantyPackageCode,
                        pw.RegisterDate, pw.CreatedDate, pw.WarrantyType, pw.RegisteredFrom, pw.IsValid, pw.[Description]
                    FROM pa_WarrantyWaiting pw
                    WHERE pw.AgencyID = @AgencyID
                    ";
            return ExecuteReader(query, CommandType.Text, new SqlParameter("@AgencyID", agencyID));
        }
        
        public static pa_WarrantyWaiting GetByID(int id)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                return context.pa_WarrantyWaitings.FirstOrDefault(p => p.WarrantyWattingID == id);
            }
        }

        public static string GetCaption(string columnName)
        {
            switch (columnName)
            {
                case "Agency":
                    return "Đại lý";
                case "ModelCode":
                    return "Model";
                case "ProductCode":
                    return "Số máy";
                case "CustomerPhone":
                    return "Điện thoại KH";
                case "WarrantyPackageCode":
                    return "Gói GH";
                case "RegisterDate":
                    return "Ngày mua hàng";
                case "WarrantyType":
                    return "Loại ĐK";
                case "CreatedDate":
                    return "Ngày ĐK";
                case "ConfirmDate":
                    return "Ngày duyệt";
                case "RegisteredFrom":
                    return "ĐK qua";
                case "Detail":
                    return "Thời hạn bảo hành";
                case "Description":
                    return "Ghi chú";
                case "IsValid":
                    return "Dữ liệu hợp lệ";
            }
            return "";
        }

        public static void DeleteByID(int id)
        {
            try
            {
                using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
                {
                    context.pa_WarrantyWaitings.DeleteAllOnSubmit(context.pa_WarrantyWaitings.Where(p => p.WarrantyWattingID == id));
                    context.SubmitChanges();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// Insert và update dữ liệu vào bảng từ file
        /// </summary>
        /// <param name="filePath">tên file excel chứa dữ liệu</param>
        /// <returns></returns>
        public static int BulkInsert(string filePath, pa_Agency agency)
        {
            try
            {
                DataTable dtSource = new DataTable();
                using (SqlBulkCopy bc = new SqlBulkCopy(AppSetting.ConnectionString))
                {
                    bc.BulkCopyTimeout = 0;
                    bc.BatchSize = 1000;
                    bc.DestinationTableName = "pa_WarrantyWaiting";
                    dtSource = ExcelHelper.ReadFromFile(filePath);
                    //using (OleDbConnection Oleconn = GetOleDbConnection(filePath))
                    //{
                    //    Oleconn.Open();
                    //    var dtSchema = Oleconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    //    var sheet1 = dtSchema.Rows[0].Field<string>("TABLE_NAME");
                    //    using (OleDbCommand Olecmd = Oleconn.CreateCommand())
                    //    {
                    //        Olecmd.CommandText = string.Format(@"SELECT * FROM [{0}]", sheet1);
                    //        dtSource.Load(Olecmd.ExecuteReader());
                    //    }
                    //}
                    TransformTable(dtSource, agency);
                    foreach (DataColumn col in dtSource.Columns)
                        bc.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    bc.WriteToServer(dtSource);
                }
                return dtSource.Rows.Count;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private static void TransformTable(DataTable dtSource, pa_Agency agency)
        {
            if (agency.FullControl)
            {
                while (dtSource.Columns.Count > 6)
                    dtSource.Columns.RemoveAt(6);
            }
            else
            {
                while (dtSource.Columns.Count > 4)
                    dtSource.Columns.RemoveAt(4);
            }
            if (!agency.FullControl)
            {
                dtSource.Columns.Add("WarrantyType", typeof(string));
                dtSource.Columns.Add("WarrantyPackageCode", typeof(string));
            }
            dtSource.Columns.Add("AgencyID", typeof(int));
            dtSource.Columns.Add("IsValid", typeof(bool));
            dtSource.Columns.Add("Approved", typeof(bool));
            dtSource.Columns.Add("CreatedDate", typeof(DateTime));
            dtSource.Columns.Add("ModifiedDate", typeof(DateTime));
            dtSource.Columns.Add("RegisteredFrom", typeof(string));
            dtSource.Columns.Add("Description", typeof(string));
            dtSource.Columns[0].ColumnName = "CustomerPhone";
            dtSource.Columns[1].ColumnName = "ModelCode";
            dtSource.Columns[2].ColumnName = "ProductCode";
            dtSource.Columns[3].ColumnName = "RegisterDate";
            dtSource.Columns[4].ColumnName = "WarrantyType";
            dtSource.Columns[5].ColumnName = "WarrantyPackageCode";
            
            DateTime now = DateTime.Now;
            foreach (DataRow row in dtSource.Rows)
            {
                string phoneNumber = row["CustomerPhone"].ToString();
                row["CustomerPhone"] = phoneNumber.ToNormalPhoneNumber();
                row["ModelCode"] = row["ModelCode"].ToString().ToUpper();
                row["ProductCode"] = row["ProductCode"].ToString().ToUpper();
                row["AgencyID"] = agency.AgencyID;
                row["Approved"] = false;
                row["CreatedDate"] = now;
                row["RegisteredFrom"] = eRegisteredFrom.WEB;
                if (!agency.FullControl)
                    row["WarrantyType"] = eWarrantyType.DK;
                row["WarrantyPackageCode"] = row["WarrantyPackageCode"].ToString().ToUpper();
                row["IsValid"] = true;
                row["Approved"] = false;
                if (Convert.ToDateTime(row["RegisterDate"]) > now)
                {
                    row["IsValid"] = false;
                    row["Description"] = row["Description"].ToString() + "Dữ liệu ngày tháng không hợp lệ";
                }
                if (string.IsNullOrWhiteSpace(phoneNumber) || !OperatorPrefixCode.ValidatePhoneNumber(phoneNumber))
                {
                    row["IsValid"] = false;
                    row["Description"] = row["Description"].ToString() + "Số điện thoại không hợp lệ. ";
                }
                string warrantyType = row["WarrantyType"].ToString();
                string packageCode = row["WarrantyPackageCode"].ToString();
                string model = row["ModelCode"].ToString();
                if (warrantyType != eWarrantyType.DK && warrantyType != eWarrantyType.GH)
                {
                    row["IsValid"] = false;
                    row["Description"] = row["Description"].ToString() + "Loại ĐK không hợp lệ. ";
                }
                else
                {
                    if (warrantyType == eWarrantyType.GH)
                    {
                        if (!pa_WarrantyPackage.CheckExists(packageCode))
                        {
                            row["IsValid"] = false;
                            row["Description"] = row["Description"].ToString() + "Gói GH không tồn tại. ";
                        }
                    }
                    else if (packageCode != "")
                    {
                        row["IsValid"] = false;
                        row["Description"] = row["Description"].ToString() + "DKBH gói gia hạn phải để trống. ";
                    }
                }
                if (!pa_Model.CheckExists(model))
                {
                    row["IsValid"] = false;
                    row["Description"] = row["Description"].ToString() + "Model không tồn tại. ";
                }
            }
        }

        public static void SetApproved(int warrantyWattingID)
        {
            ExecuteProc("usp_pa_WarrantyWaiting_Approved", new SqlParameter("@WarrantyWattingID", warrantyWattingID));
        }

        public static void DeleteAllNotValidateData(int agencyID)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                context.pa_WarrantyWaitings.DeleteAllOnSubmit(
                    context.pa_WarrantyWaitings.Where(p => p.AgencyID == agencyID && p.IsValid == false)
                );
                context.SubmitChanges();
            }
        }
    }
}
